﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ELS.User;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using System.Windows.Forms;
using System.Collections;
using System.Diagnostics;
using System.Collections.Specialized;

namespace ELS.BackupRestore
{
    class BackupRestoreDataAccess
    {
        string connectionString = UserCommon.connectionString;
        internal DataTable SelectAllFile()
        {
            DataTable dtAllFile = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand("GetFile", sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter adap = new SqlDataAdapter(cmd);                    
                    adap.Fill(dtAllFile);
                    return dtAllFile;
                }
                catch (Exception ex) 
                {
                    return null;
                }
                finally 
                { 
                    sqlConnection.Close(); 
                }
            }
        }

        internal int InsertNewFile(string fileName, byte[] buffer, int fileSize, string fileType)
        {
            int rowInsert = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("AddFile", sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    sqlConnection.Open();
                    cmd.Parameters.AddWithValue("@FileName", fileName);
                    cmd.Parameters.AddWithValue("@FileBody", buffer);
                    cmd.Parameters.AddWithValue("@FileSize", fileSize);
                    cmd.Parameters.AddWithValue("@FileType", fileType);                    
                    rowInsert = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    return rowInsert;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return rowInsert;
        }

        internal DataTable SelectAllTableName()
        {
            DataTable dtAllTables = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand("GetAllTableName", sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter adap = new SqlDataAdapter(cmd);
                    adap.Fill(dtAllTables);
                    return dtAllTables;
                }
                catch (Exception ex) 
                {
                    return null;
                }
                finally 
                { 
                    sqlConnection.Close(); 
                }
            }
        }

        internal void CreateBackUpFile(string currentFileName)
        {
            try
            {
                if (File.Exists(currentFileName))
                    File.Delete(currentFileName);

                SqlConnection connect = new SqlConnection(UserCommon.connectionString);                
                Server srv = new Server(new ServerConnection(connect));
                string databaseName = UserCommon.connectionString.Split(';')[1].Split('=')[1].ToString();
                Database dbs = srv.Databases[databaseName];

                List<Urn> list = new List<Urn>();
                DataTable dataTable = dbs.EnumObjects(DatabaseObjectTypes.Table);
                foreach (Table tb in dbs.Tables)
                {
                    if (tb.Name != UserCommon.TableBackupDb)
                    {
                        list.Add(new Urn((string)tb.Urn));
                    }
                }
                
                Scripter scripter = new Scripter();
                scripter.Server = srv;                
                scripter.Options.ScriptDrops = false;
                scripter.Options.ScriptSchema = true;
                scripter.Options.FileName = currentFileName;
                scripter.Options.EnforceScriptingOptions = true;
                scripter.Options.WithDependencies = true;
                scripter.Options.Indexes = true;
                scripter.Options.AppendToFile = true;
                scripter.Options.IncludeHeaders = true;
                scripter.Options.ScriptData = true;

                scripter.Options.ClusteredIndexes = true;
                scripter.Options.NonClusteredIndexes = true;
                scripter.Options.Default = true;
                scripter.Options.FullTextIndexes = true;
                scripter.Options.SchemaQualify = true;
                scripter.Options.Statistics = true;
                scripter.Options.Triggers = true;
                scripter.Options.DriAll = true;

                scripter.EnumScript(list.ToArray());
            }
            catch (NullReferenceException e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
        }

        //public string ScriptDatabase()
        //{
        //    StringBuilder sb = new StringBuilder();
        //    SqlConnection connect = new SqlConnection(UserCommon.connectionString);
        //    Server server = new Server(new ServerConnection(connect));
        //    Database dbs = server.Databases["ELS_v5"];

        //    Scripter scripter = new Scripter(server);                       

        //    scripter.Options.ScriptDrops = false;
        //    scripter.Options.ScriptSchema = true;
        //    //scripter.Options.FileName = currentFileName;
        //    scripter.Options.EnforceScriptingOptions = true;
        //    scripter.Options.WithDependencies = true;
        //    scripter.Options.Indexes = true;
        //    scripter.Options.AppendToFile = true;
        //    scripter.Options.IncludeHeaders = true;
        //    scripter.Options.ScriptData = true;

        //    scripter.Options.ClusteredIndexes = true;
        //    scripter.Options.NonClusteredIndexes = true;
        //    scripter.Options.Default = true;
        //    scripter.Options.FullTextIndexes = true;
        //    scripter.Options.SchemaQualify = true;
        //    scripter.Options.Statistics = true;
        //    scripter.Options.Triggers = true;
        //    scripter.Options.DriAll = true;


        //    var smoObjects = new Urn[1];
        //    foreach (Table t in dbs.Tables)
        //    {
        //        smoObjects[0] = t.Urn;
        //        if (t.IsSystemObject == false)
        //        {
        //            StringCollection sc = scripter.Script(smoObjects);

        //            foreach (var st in sc)
        //            {
        //                sb.Append(st);
        //            }
        //        }
        //    }
        //    return sb.ToString();
        //}

        internal int DeleteBackUpFile(string fileId)
        {
            int rowDelete = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand("DeleteFile", sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Id", fileId);
                    rowDelete = cmd.ExecuteNonQuery();
                    return rowDelete;
                }
                catch (Exception ex)
                {
                    return rowDelete;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int RestoreDb(int currentFileId)
        {
            int rowEffect = -1;
            string currentFileName = UserCommon.fileBackUpPath + UserCommon.TempRestoreFilename;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();

                    Server server = new Server(new ServerConnection(sqlConnection));
                    string dropDataFileName = UserCommon.filePathDropTable;
                    FileInfo fileDropDataFileName = new FileInfo(dropDataFileName);
                    string scriptDropDataFileName = fileDropDataFileName.OpenText().ReadToEnd();
                    server.ConnectionContext.ExecuteNonQuery(scriptDropDataFileName);
                    fileDropDataFileName.OpenText().Close();

                    SqlCommand cmd = new SqlCommand("GetFileById", sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Id", currentFileId);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        byte[] bytes = (byte[])reader.GetValue(2);
                        MemoryStream me = new MemoryStream(bytes);
                        FileStream fs = new FileStream(currentFileName, FileMode.Create);
                        fs.Write(bytes, 0, bytes.Length);
                        
                        fs.Flush();
                        fs.Close();
                        me.Close();
                    }
                    reader.Close();                    

                    FileInfo fileRestore = new FileInfo(currentFileName);                    
                    //Open the file to read from.
                    StreamReader sr = fileRestore.OpenText();                    
                    string scriptFileRestore = sr.ReadToEnd();
                    sr.Close();
                    fileRestore.Delete();

                    rowEffect = server.ConnectionContext.ExecuteNonQuery(scriptFileRestore);
                    return 1;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message + "-" + e.Data + "-" + e.StackTrace);
                    return rowEffect;
                }
                finally
                {
                    sqlConnection.Close();                    
                }
            }
        }
                        
    }
}
